![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Rule-Based ApproachThe rule-based approach to Oracle optimization is the simpler of the two methods. In the rule-based approach, the execution plan is derived by examining the available paths and comparing them against a table of the rank of these paths. The table of costs is shown in Table 27.1.
Because the rule-based approach is based simply on the SQL statements themselves, it is unnecessary to have any statistics about the database tables. The rule-based approach follows these steps to determine the execution plan:
In this way, the rule-based optimization approach is very efficient and works well. However, if statistics are available for your tables, clusters, or indexes, the cost-based approach can be very efficient. Cost-Based ApproachThe cost-based approach to optimization uses information about your database to choose the most efficient execution plan. During the normal operation of the RDBMS, or when you execute the ANALYZE command, statistics are gathered on the data distribution and storage characteristics for your database tables, clusters, and indexes. The cost-based optimizer uses this information to determine the most optimal execution plan. This approach is done in three steps:
The default goal of the cost-based optimizer is to generate an execution plan that gives the best throughput. You can specify other optimization goals, including the following:
By choosing the optimization approach that best suits your particular installation and application, the performance of your SQL statements can be tuned to specifically meet your needs. Using the ANALYZE CommandYou can use the ANALYZE command to gather statistics about your system that can be used for the cost-based optimizer. This command can be used not only for statistics gathering but for other purposes as well. The ANALYZE command can be used to do the following:
The statistics gathered by the ANALYZE command can better help the optimizer make the correct choice in determining an execution plan. How To Run the ANALYZE CommandHow you run the ANALYZE command is determined by the type of statistics or analysis you want to perform. The ANALYZE command can be used in several different modes. The mode you choose depends on the data you want to gather as well as on the configuration of your system. Using ANALYZE To Gather Statistics You can use the ANALYZE command to gather statistics in one of two modes. The first mode scans the entire table, cluster, or index and calculates statistics exactly, based on your data. Although this is the most accurate method, it requires enough temporary space to hold and sort all the rows of the table or cluster (no space is required for an index). Computing the statistics also uses a great deal of system resources. The second mode of the ANALYZE command estimates statistics. This method performs a sampling of the table, cluster, or index in order to estimate statistics. In this method, the entire table or cluster is not scanned; a portion of the data is used to determine the statistics. The amount of data used can be specified when you invoke the ANALYZE command. Using ANALYZE To Compute Exact Statistics To use the ANALYZE command to compute exact statistics, invoke ANALYZE with one of the following syntaxes. For Tables: ANALYZE TABLE table_name COMPUTE STATISTICS; For Clusters: ANALYZE CLUSTER cluster_name COMPUTE STATISTICS; For Indexes: ANALYZE INDEX index_name COMPUTE STATISTICS;
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |